﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using log4net;
using System.Data;
using MySql.Data.MySqlClient;
using Dapper;

namespace PrintDemo.DBUtil
{
    public class MySqlHelper
    {
        ILog log = LogManager.GetLogger("MySqlHelper");
        public static string _connString;

        public MySqlHelper(String hostIp, String database, String user, String password)
        {
            _connString = String.Format("Server={0};UserId={1}; Password={2}; Database={3};",
               hostIp, user, password, database);
            //_connString = String.Format("server={0};user id={1}; password={2}; database={3}; pooling=true;charset=utf8;Allow User Variables=True;DefaultCommandTimeout=60;",
               //hostIp, user, password, database);
        }

        public MySqlHelper()
        {
            _connString = "server=192.168.52.132;user id=root; password=wuyou; database=lingzhidb; pooling=true;charset=utf8;Allow User Variables=True;DefaultCommandTimeout=60;";
        }

        public static IDbConnection Conn
        {
            get
            {
                SimpleCRUD.SetDialect(SimpleCRUD.Dialect.MySQL);
                return new MySqlConnection(_connString);

            }
        }

        public MySqlHelper(string connectionString)
        {
            _connString = connectionString;
        }

        #region Connection

        //public static IDbConnection Conn
        //{
        //    get
        //    {
        //        SimpleCRUD.SetDialect(SimpleCRUD.Dialect.MySQL);
        //        return new MySqlConnection(_connString);

        //    }
        //}

        public static MySqlConnection getConnectionEx()
        {
            MySqlConnection conn = null;
            try
            {
                conn = new MySqlConnection(_connString);
            }
            catch (MySqlException ex)
            {
                conn = null;
                //log.Error("连接失败！", ex);
            }
            return conn;
        }
        public MySqlConnection getConnection()
        {
            MySqlConnection conn = null;
            try
            {
                conn = new MySqlConnection(_connString);
                ConnectionState st = conn.State;

                conn.Open();


            }
            //catch (MySqlException ex)
            catch (Exception ex)
            {
                conn = null;
                log.Error("连接失败！", ex);
            }
            return conn;
        }

        private void closeConnection(MySqlConnection conn)
        {
            if (conn != null)
            {
                conn.Close();
            }
        }

        private void closeConnection(MySqlCommand cmd)
        {
            if (cmd.Connection != null)
            {
                cmd.Connection.Close();
            }
        }
        #endregion

        #region ExecuteProcedure

        public int ExecuteProcedureNonQuery(string procedureName, string[] paramNames, object[] paramValues)
        {
            int result = 0;
            MySqlCommand cmd = PrepareCommand(procedureName, CommandType.StoredProcedure, paramNames, paramValues);
            if (cmd.Connection != null)
            {
                result = cmd.ExecuteNonQuery();
                closeConnection(cmd);
            }
            return result;
        }

        public object ExecuteProcedureForScalar(string procedureName, string[] paramNames, object[] paramValues)
        {
            object result = null;
            MySqlCommand cmd = PrepareCommand(procedureName, CommandType.StoredProcedure, paramNames, paramValues);
            if (cmd.Connection != null)
            {
                result = cmd.ExecuteScalar();
                closeConnection(cmd);
            }
            return result;
        }

        public DataSet ExecuteProcedureForDataSet(string procedureName, string[] paramNames, object[] paramValues)
        {
            DataSet ds = null;
            try
            {
                MySqlCommand cmd = PrepareCommand(procedureName, CommandType.StoredProcedure, paramNames, paramValues);
                if (cmd.Connection != null)
                {
                    ds = new DataSet();
                    MySqlDataAdapter da = new MySqlDataAdapter(cmd);
                    da.Fill(ds);
                    closeConnection(cmd);
                }
            }
            catch (Exception ex)
            {
                log.Error("查询数据集合失败！", ex);
            }
            return ds;
        }
        #endregion

        #region ExecuteSql

        public int ExecuteSqlNonQuery(string sqlString)
        {
            int result = 0;
            MySqlCommand cmd = null;
            try
            {
                cmd = PrepareCommand(sqlString, CommandType.Text, null, null);
                if (cmd.Connection != null)
                {
                    result = cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                log.Error("数据库事物操作异常！", ex);
            }
            finally
            {
                closeConnection(cmd);
            }
            return result;
        }

        public int ExecuteSqlNonQuery(string[] sqlString)
        {
            int result = 0;
            MySqlTransaction mst = null;
            MySqlCommand cmd = null;
            try
            {
                MySqlConnection conn = getConnection();
                mst = conn.BeginTransaction();
                cmd = new MySqlCommand();
                cmd.Connection = conn;
                cmd.Transaction = mst;
                cmd.CommandType = CommandType.Text;
                foreach (string st in sqlString)
                {
                    cmd.CommandText = st;
                    result = cmd.ExecuteNonQuery();
                }
                mst.Commit();

            }
            catch (Exception ex)
            {
                mst.Rollback();
                log.Error("数据库事物操作异常！", ex);
            }
            finally
            {
                closeConnection(cmd);
            }

            return result;
        }

        public bool ExecuteSqlNonQuery(Queue<string> sqlString, int timeOut = 60)
        {
            bool success = true;
            int result = 0;
            MySqlTransaction mst = null;
            MySqlCommand cmd = null;
            try
            {
                MySqlConnection conn = getConnection();
                mst = conn.BeginTransaction();
                cmd = new MySqlCommand();
                cmd.Connection = conn;
                cmd.Transaction = mst;
                cmd.CommandType = CommandType.Text;
                cmd.CommandTimeout = timeOut;
                while (sqlString.Count > 0)
                {
                    cmd.CommandText = sqlString.Dequeue();
                    result = cmd.ExecuteNonQuery();
                }
                mst.Commit();
            }
            catch (Exception ex)
            {
                mst.Rollback();
                success = false;
                log.Error("数据库事物操作异常！", ex);
            }
            finally
            {
                closeConnection(cmd);
            }
            return success;
        }

        //2017-05-31 新增ExecuteSqlNonQuery 周亚军
        public int ExecuteSqlNonQuery(string sqlString, params MySqlParameter[] cmdParms)
        {
            int result = 0;
            MySqlCommand cmd = null;
            try
            {
                cmd = PrepareCommand(sqlString, CommandType.Text, cmdParms);
                if (cmd.Connection != null)
                {
                    result = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                }
            }
            catch (Exception ex)
            {
                log.Error("数据库事物操作异常！", ex);
            }
            finally
            {
                closeConnection(cmd);
            }
            return result;
        }

        public object ExecuteSqlForScalar(string sqlString, params MySqlParameter[] cmdParms)
        {
            object result = null;
            MySqlCommand cmd = null;
            try
            {
                cmd = PrepareCommand(sqlString, CommandType.Text, cmdParms);
                if (cmd.Connection != null)
                {
                    result = cmd.ExecuteScalar();
                    cmd.Parameters.Clear();
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, ex);
            }
            finally
            {
                closeConnection(cmd);
            }
            return result;
        }


        public object ExecuteSqlForScalar(string sqlString)
        {
            object result = null;
            MySqlCommand cmd = null;
            try
            {
                cmd = PrepareCommand(sqlString, CommandType.Text, null, null);
                if (cmd.Connection != null)
                {
                    result = cmd.ExecuteScalar();
                }
            }
            catch (Exception ex)
            {
                log.Error("查询数据存在异常！", ex);
            }
            finally
            {
                closeConnection(cmd);
            }
            return result;
        }

        public MySqlDataReader ExecuteSqlForReader(string sqlString, out MySqlConnection conn)
        {
            MySqlDataReader reader = null;
            MySqlCommand cmd = PrepareCommand(sqlString, CommandType.Text, null, null);
            conn = cmd.Connection;
            if (cmd.Connection != null)
            {
                reader = cmd.ExecuteReader();
            }
            return reader;
        }

        public MySqlDataReader ExecuteSqlForReaderBinary(string sqlString, out MySqlConnection conn)
        {
            MySqlDataReader reader = null;
            MySqlCommand cmd = PrepareCommand(sqlString, CommandType.Text, null, null);
            conn = cmd.Connection;
            if (cmd.Connection != null)
            {
                reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess);
            }

            return reader;
        }

        public DataSet ExecuteSqlForDataSet(string sqlString)
        {
            DataSet ds = null;
            MySqlCommand cmd = null;
            try
            {
                cmd = PrepareCommand(sqlString, CommandType.Text, null, null);
                if (cmd.Connection != null)
                {
                    ds = new DataSet();
                    MySqlDataAdapter da = new MySqlDataAdapter(cmd);
                    da.Fill(ds);
                }
            }
            catch (Exception ex)
            {
                log.Error("查询数据集合异常！", ex);
            }
            finally
            {
                closeConnection(cmd);
            }
            return ds;
        }

        public DataSet ExecuteSqlForDataSet(string sqlString, params MySqlParameter[] cmdParms)
        {
            DataSet ds = null;
            MySqlCommand cmd = null;
            try
            {
                cmd = PrepareCommand(sqlString, CommandType.Text, cmdParms);
                if (cmd.Connection != null)
                {
                    ds = new DataSet();
                    MySqlDataAdapter da = new MySqlDataAdapter(cmd);
                    da.Fill(ds);
                    cmd.Parameters.Clear();
                }
            }
            catch (Exception ex)
            {
                log.Error("查询数据集合异常！", ex);
            }
            finally
            {
                closeConnection(cmd);
            }
            return ds;
        }

        public string ExecuteSqlForString(string sql)
        {
            string res = string.Empty;
            MySqlCommand cmd = null;
            try
            {
                cmd = PrepareCommand(sql, CommandType.Text, null, null);
                object obj = cmd.ExecuteScalar();
                if (obj != null)
                {
                    res = obj.ToString();
                }
            }
            catch (Exception ex)
            {
                log.Error("查询数据字符串异常！", ex);
            }
            finally
            {
                closeConnection(cmd);
            }
            return res;
        }

        #endregion

        #region PrepareCommand
        private MySqlCommand PrepareCommand(string cmdText, CommandType cmdType, string[] paramNames, object[] paramValues)
        {
            MySqlCommand cmd = new MySqlCommand();
            cmd.Connection = getConnection();
            if (cmd.Connection != null)
            {
                cmd.CommandType = cmdType;
                cmd.CommandText = cmdText;
                if (paramNames != null && paramValues != null)
                {
                    if (paramNames.Length != paramValues.Length)
                    {
                        throw new Exception("参数个数和参数值个数不一样！");
                    }
                    for (int index = 0; index < paramNames.Length; index++)
                    {
                        cmd.Parameters.Add(new MySqlParameter(paramNames[index], paramValues[index]));
                    }
                }
            }
            return cmd;
        }
        private MySqlCommand PrepareCommand(string cmdText, CommandType cmdType, MySqlParameter[] cmdParms)
        {
            MySqlCommand cmd = new MySqlCommand();
            cmd.Connection = getConnection();
            if (cmd.Connection != null)
            {
                cmd.CommandType = cmdType;
                cmd.CommandText = cmdText;
                if (cmdParms != null)
                {
                    foreach (MySqlParameter parameter in cmdParms)
                    {
                        if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                            (parameter.Value == null))
                        {
                            parameter.Value = DBNull.Value;
                        }
                        cmd.Parameters.Add(parameter);
                    }
                }
            }
            return cmd;
        }

        #endregion
    }
}
